''' 滴滴打灰后端应用服务 数据库操作API （CMS管理） '''
import sys,traceback,logging,datetime
import pymysql
from dbutils.pooled_db import PooledDB 
from utils.db_config import mysqlInfo


logger = logging.getLogger('cmsservice')


# 创建数据库连接
def connect_db():
    pool=PooledDB(creator=pymysql,mincached=2, maxcached=5,maxshared=3, maxconnections=6, blocking=True,host=mysqlInfo['host'],port=mysqlInfo['port'],
        user=mysqlInfo['user'],password=mysqlInfo['password'],database=mysqlInfo['database'],charset=mysqlInfo['charset'])
    return pool.connection()


# 查询轮播图(广告图)
def cmsadlist(apId):
    conn = connect_db()
    cur = conn.cursor()
    adlist = []
    try:
        sql = "select ad_id AS 'adId', ap_id AS 'apId', title AS 'title', pic AS 'pic', url_type AS 'urlType', url AS 'url', status AS 'status', sort AS 'sort', if_del AS 'ifDel'" \
              " from cms_ad where ap_id = %s "
        cur.execute(sql,(apId))
        for row in cur.fetchall():
            rowdict = {}
            rowdict['adId'] = row[0]
            rowdict['apId'] = row[1]
            rowdict['title'] = row[2]
            rowdict['pic'] = row[3]
            rowdict['urlType'] = row[4]
            rowdict['url'] = row[5]
            rowdict['status'] = row[6]
            rowdict['sort'] = row[7]
            rowdict['ifDel'] = row[8]
            adlist.append(rowdict)
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return adlist
    
# 查询公告列表
def cmsnoticelist():
    conn = connect_db()
    cur = conn.cursor()
    noticelist = []
    try:
        sql = "select notice_id AS 'noticeId', title AS 'title', pic AS 'pic', summary AS 'summary', content AS 'content', if_readed AS 'ifReaded', status AS 'status', sort AS 'sort', if_del AS 'ifDel'" \
              " from cms_app_notice "
        cur.execute(sql)
        for row in cur.fetchall():
            rowdict = {}
            rowdict['noticeId'] = row[0]
            rowdict['title'] = row[1]
            rowdict['pic'] = row[2]
            rowdict['summary'] = row[3]
            rowdict['content'] = row[4]
            rowdict['ifReaded'] = row[5]
            rowdict['status'] = row[6]
            rowdict['sort'] = row[7]
            rowdict['ifDel'] = row[8]
            noticelist.append(rowdict)
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return noticelist

# 查询公告详情
def noticedetail(noticeId):
    conn = connect_db()
    cur = conn.cursor()
    notice = {}
    try:
        sql = "select notice_id AS 'noticeId', title AS 'title', pic AS 'pic', summary AS 'summary', content AS 'content', if_readed AS 'ifReaded', status AS 'status', sort AS 'sort', if_del AS 'ifDel'" \
              " from cms_app_notice where notice_id = %s "
        cur.execute(sql,(noticeId))
        for row in cur.fetchall():
            rowdict = {}
            rowdict['noticeId'] = row[0]
            rowdict['title'] = row[1]
            rowdict['pic'] = row[2]
            rowdict['summary'] = row[3]
            rowdict['content'] = row[4]
            rowdict['ifReaded'] = row[5]
            rowdict['status'] = row[6]
            rowdict['sort'] = row[7]
            rowdict['ifDel'] = row[8]
            notice = rowdict
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return notice

# 关于我们
def aboutUsdetail():
    conn = connect_db()
    cur = conn.cursor()
    rowdict = {}
    try:
        sql = "select about_id AS aboutId, title AS title, logo AS logo, content AS content, service_phone AS servicePhone, copyright AS copyright, status AS status from cms_about_us"
        cur.execute(sql)
        for row in cur.fetchall():
            rowdict = {}
            rowdict['aboutId'] = row[0]
            rowdict['title'] = row[1]
            rowdict['logo'] = row[2]
            rowdict['content'] = row[3]
            rowdict['servicePhone'] = row[4]
            rowdict['copyright'] = row[5]
            rowdict['status'] = row[6]
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return rowdict

# 关于我们
def helpdetail(param):
    conn = connect_db()
    cur = conn.cursor()
    rowdict = {}
    try:
        sql = "sselect help_id AS helpId, title AS title, pic AS pic, category_id AS categoryId,summary AS summary, content AS content, version AS version, status AS status, sort AS sort from cms_help where category_id = %s"
        cur.execute(sql,(param['categoryId']))
        for row in cur.fetchall():
            rowdict = {}
            rowdict['helpId'] = row[0]
            rowdict['title'] = row[1]
            rowdict['pic'] = row[2]
            rowdict['categoryId'] = row[3]
            rowdict['summary'] = row[4]
            rowdict['content'] = row[5]
            rowdict['version'] = row[6]
            rowdict['status'] = row[7]
            rowdict['sort'] = row[8]
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return rowdict
